/*
 * WarrantyDAO.java
 *
 * Created on March 22, 2007, 1:45 PM
 *
 */

package edu.byu.isys413.group1E.data;

import edu.byu.isys413.group1E.*;
import java.sql.*;
import java.util.ArrayList;

/** This class is to read and write WarrantyBO information to and from the DB
 *
 * @author Travis Kulbeth
 */
public class WarrantyDAO{
    /** Contains an instance of WarrantyDAO*/
    private static WarrantyDAO instance = null;
    
    /** Creates a new instance of WarrantyDAO */
    public WarrantyDAO() {
    }
    
    /**Retrieves an instance of the WarrantyDAO object
     *@return instance of WarrantyDAO
     */
    public static synchronized WarrantyDAO getInstance() {
        if (instance == null) {
            instance = new WarrantyDAO();
        }
        return instance;
    }
    
    ///////////////////////////////////////////////////////////////////////////////
    /////////   CREATE
    /**
     * Creates a new WarrantyBO object with a newly generated GUID.
     * 
     * @return a newly created WarrantyBO with unique GUID
     * @throws java.lang.Exception to catch any errors in the creation process.
     */
    public BusinessObject create() throws Exception{
        String id = GUID.generate();
        WarrantyBO bo = new WarrantyBO(id);
        Cache c = Cache.getInstance();
        c.put(bo.getId(), bo);
        return bo;
    }
    
    /**
     * This is a public read statement.  It loads an existing Warranty record
     * from the database based on the RevSrcID and returns a WarrantyBO.
     * 
     * @return a WarrantyBO containing the information extracted from the requested
     * tuple in the DB.
     * @param id contains the unique identifier for the record to be read from the DB.
     * @throws edu.byu.isys413.group1E.DataException to catch any errors with the incoming data
     * @throws edu.byu.isys413.group1E.ConnectionPoolException to catch errors when connecting to the DB
     */
     public BusinessObject read(String id) throws SQLException, DataException, ConnectionPoolException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        BusinessObject war = (WarrantyBO)c.get(id);
        if (war != null){
            return war;                                                        // if so, return it immediately
        }
        
        //otherwise, get it from the database
        try {
            Connection conn = ConnectionPool.getInstance().get();             // retrieve a database connection from the pool
            war = (WarrantyBO)this.read(id, conn);                                              // call read with a connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);                       // release the connection
        }catch (SQLException e) {
            //rollback
            throw new DataException("Could not retrieve record for id = " + id, e);
        }catch (ConnectionPoolException x) {
            //rollback
            throw new ConnectionPoolException("Could not establish connection to database.", x);
        }
        
        // release the connection back to the pool
        return war;
    }
    
    /**
     * This is a public read statement that is called after a connection has been established with the DB.
     * It loads an existing Warranty record from the database based on the RevSrcID.
     *
     *This class calls its super class, RevenueSourceDAO, to read in the information that is common across
     *all Revenue Sources, then retrieves the unique information.
     * 
     * @return WarrantyBO
     * @param id contains the given id for the Warranty to be read
     * @param conn contains an already open connection to the DB
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors with the connection
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors from the SQL execution
     */
    public synchronized BusinessObject read(String id, Connection conn) throws SQLException, DataException {
        // check to see if id in the cache
        Cache c = Cache.getInstance();
        WarrantyBO war = (WarrantyBO)c.get(id);
        if (war != null){
            return war;                                                        // if so, return it immediately
        }
        
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        war = new WarrantyBO();
        
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM Warranty WHERE WarrantyID = '" + id + "'");
        conn.commit();
        
        //warild the object from the result set
        if (rs.next()) {
            war.setId(rs.getString("WarrantyID"));
            war.setWLength(rs.getInt("wLength"));
            war.setAlreadyInDB(true);
            war.setIsDirty(false);
            
            // save to the cache
            c.put(war.getId(), war);
        }else{
            throw new DataException("Object was not found in the database.");
        }
        stmt.close();
        // return the BO
        return war;
    }
    
    /**This is a public method to retrieve a list of all Warranty items in the DB.
     * 
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors when connecting to the DB.
     * @return a list of all Warrantys from the DB.
     */
    public synchronized ArrayList<BusinessObject> readAll() throws DataException, SQLException, ConnectionPoolException{
        ArrayList<BusinessObject> list = new ArrayList<BusinessObject>();
        //now warild the query
        String query = "SELECT * FROM Warranty";
        
        Connection conn = ConnectionPool.getInstance().get();
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery(query);
        conn.commit();
        
        //extract the elements from the result set and warild a list of results
        //warild the object from the result set
        while (rs.next()) {
            WarrantyBO war = new WarrantyBO(rs.getString("WarrantyID"));
            war.setWLength(rs.getInt("wLength"));
            war.setAlreadyInDB(true);
            war.setIsDirty(false);
            
            // save to the list
            list.add(war);
        }//while
        
        stmt.close();
        ConnectionPool.getInstance().release(conn);
        return list;
    }
    
    /**
     * This method is really a part of save(bo, conn) in the super class.  It could be
     * embedded directly in it, wart I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  wart
     * logically, it's really just a part of save.
     * 
     *This method is called by the save method in the super class after the common 
     *RS data has been saved to the DB.
     * 
     * @param bo contains the WarrantyBO to be updated to the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors with the connection
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     */
    protected void update(BusinessObject bo, Connection conn) throws SQLException, DataException {
        //update the DB
        WarrantyBO war = (WarrantyBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("UPDATE Warranty SET wLength = ? WHERE WarrantyID = ?");
        
        pstmt.setInt(1, war.getWLength());
        pstmt.setString(2, war.getId());
        pstmt.executeUpdate();
        pstmt.close();
        war.setAlreadyInDB(true);
    }// end update
    
    /**
     * This method is really a part of save(bo, conn) in the super class.  It could be
     * embedded directly in it, wart I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  wart
     * logically, it's really just a part of save.
     * 
     *This method is called by the save method in the super class after the common 
     *RS data has been saved to the DB.
     * 
     * @param bo contains the WarrantyBO to be inserted into the DB
     * @param conn contains the connection already established with the DB
     * @throws edu.byu.isys413.group1E.ConnectionPoolException catches any errors with the connection
     * @throws edu.byu.isys413.group1E.DataException catches any errors with incoming data
     * @throws java.sql.SQLException catches any errors with SQL execution
     */
    synchronized void insert(BusinessObject bo, Connection conn) throws SQLException, DataException {
        //update the DB
        WarrantyBO war = (WarrantyBO)bo;
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO Warranty (WarrantyID, wLength) VALUES (?, ?)");
        pstmt.setString(1, war.getId());
        pstmt.setInt(2, war.getWLength());
        pstmt.executeUpdate();
        pstmt.close();
        // tell the object that it's now in the db (so we call update next time not insert)
        war.setAlreadyInDB(true);
    }//end insert
}
